SQL Database Backup and Restore

The following information provides general guidance regarding SQL database backups and restores. If you are unfamiliar with SQL server and are unsure performing these steps, it is recommended that you contact an IT resource that is familiar with database server administration. WARNING: Performing a database restore incorrectly can cause permanent and irrevocable loss of data in your New World ERP database.

Database Backup:

  1. In SQL Management Studio right click on the database that needs to be backed up. Go to Tasks > Backup.Note: There is no need to stop the application while performing a backup of a database.

  2. Use the default settings as shown below. If there is a file in the destination portion remove it and click Add. Then click OK.

  3. Next, specify the database backup file. Click on the ... button to access the Windows explorer screen.

  4. From this screen choose the destination for the backup file and name it. (The file name needs to use underscores instead of spaces. Follow the name with a .bak extension.) Then choose the location, name the file and click OK twice.

  5. Select the Options page and make sure the defaults below are in place and click OK.

  6. When the backup is complete, the following notification will be displayed. This completes the backup process. 

  7. Take note of your MIU patch level by running this query and noting the last patch to have run. Ensure that the server you will be restoring this DB to has at least that level of patch applied in the MIU.

    a. Select * from App.DatabaseHotfixes

Database Restore:

WARNING: Performing a database restore incorrectly or without an adequate backup can cause permanent and irrevocable loss of data in your new world ERP database.

  1. Before the database can be restored, some (or all) of the new world ERP services running on the application server must be stopped in order for SQL Management Studio to gain exclusive access to the database. At a minimum, you may need to stop the following services from the Services Control Panel on the application server:

    • Logos.NET Application Service (NWSAppService.exe)
    • New World Logos Caching Service
    • New World Revenue Collection Service
    • eSuite.ePermits.NotificationService

  2. The next step will be to restore the database to live. In SQL Management Studio, right click on the database to be restored. Go to Tasks > Restore > Database. The To Database field will be automatically filled. Choose to restore From device and click the ... button.

  3. In the Source field select Device from the drop-down then click ‘…’ on the right.

  4. This will bring up a Windows explorer menu. Navigate to the backup file that needs to be restored and click OK twice.

  5. Next, click the Options tab.

  6. Under the Restore options section, Select Overwrite the existing database (WITH REPLACE).
  7. In the Recovery state, select the RESTORE WITH RECOVERY options.
  8. Uncheck Take tail-log backup before restore
  9. Next click on Files. Be sure to choose the correct restore as .mdf and .log destinations.
  10. Click OK to start the process.
  11. Go back to the General tab and make sure the Destination Database is the one you want to restore. This often changes on SQL 2012 and higher to the source database name.
  12. After you restore the database, restart the services on the Test or Application server, which was stopped earlier in the instructions.  You will also need to restart the Default Web Site in IIS.

  13. Please make sure to patch the database to its prior level on this server or apply all the current patches in the MIU post-restore.

Appendix A

Exclusive access could not be obtained because the database is in use

If you continue to receive a message that the system is failing to restore because the database is in use, perform the following steps:

1. Open IIS Manager (Start > Administrative Tools > Internet Information Services (IIS) Manager) and expand the server name > sites > right click on Default Web Site > Manage Web Site > Stop.

(After successfully restoring, be sure to come back in and start the Default Web Site again.)

2. If you are still experiencing an issue, open the Activity Monitor in SQL Management Studio.

Expand Processes and click the arrow next to the Database header to sort by the database you are trying to restore to. If you see any open processes here, right click them and select Kill Process.

If you are unable to kill all processes or are still having any issues restoring your database, reboot or shut down the test app server. Restore the database while it is rebooting or off.